from fof99 import FundAdvancedList, FundCompanyPrice, FundMultiPrice,CompanyPriceBatchAdd
import pandas as pd
from gypb.config import settings
from gypb.db import get_db,get_ddbapi
from ddbtools import BaseCRUD, DBDf,Comparator,Filter
from xqdata_ddb import upsert_attribute,DDBDataApi
import requests

appid = settings.hfn.user
appkey = settings.hfn.password

ddbapi = get_ddbapi()

def get_hfw_products(page=1, pagesize=5000):
    req = FundAdvancedList(appid, appkey)
    req.set_params(strategy_one="不限", page=page, pagesize=pagesize)
    res = req.do_request(use_df=True)
    return res


def upload_hfw_products(hfn_productinfo_df: pd.DataFrame):
    # print(hfn_productinfo_df.shape)
    date_columns = ["inception_date", "liquidate_date", "puton_date"]
    for col in date_columns:
        hfn_productinfo_df[col] = pd.to_datetime(
            hfn_productinfo_df[col], errors="coerce"
        ).fillna(pd.Timestamp("2100-01-01"))
    crud = BaseCRUD("dfs://infos", "hfn_product")
    crud.key_cols = ["register_number"]
    with get_db() as session:
        data = DBDf(
            session=session,
            db_path="dfs://infos",
            table_name="hfn_product",
            data=hfn_productinfo_df,
        )
        crud.upsert(session, data)


def get_hfw_net_value(prod_codes):
    # date = pd.Timestamp(date).strftime("%Y-%m-%d")
    # page_size = 40
    # pages = (len(prod_codes) + page_size - 1) // page_size
    if isinstance(prod_codes,str):
        prod_codes = [prod_codes]
    dfs = []
    for prod_code in prod_codes:
        # page_codes = ",".join(prod_codes[page * page_size:(page + 1) * page_size])
        if prod_code == "T05424":
            prod_code = "SZ686B"
        req = FundCompanyPrice(appid, appkey)
        req.set_params(reg_code=prod_code, start_date="2020-01-01")
        res = req.do_request(use_df=True)
        if prod_code == "SZ686B":
            prod_code = "T05424"
        res["prod_code"] = prod_code
        dfs.append(res)
    return pd.concat(dfs).sort_values("price_date")


def upload_hfw_net_value(hfn_nav_df: pd.DataFrame):
    hfn_nav_df.rename(
        columns={
            "nav": "netvalue",
            "cumulative_nav": "reinv_netvalue",
            "cumulative_nav_withdrawal": "cum_netvalue",
            "price_date": "datetime",
            "prod_code": "code",
        },
        inplace=True,
    )
    hfn_nav_df["datetime"] = pd.to_datetime(hfn_nav_df["datetime"])
    hfn_nav_df.set_index(["datetime", "code"], inplace=True)
    with get_db() as session:
        upsert_attribute(
            session,
            "product",
            hfn_nav_df,
            attributes=["netvalue", "cum_netvalue", "reinv_netvalue"],
        )

def upload_nv_to_hfv(codes,start_date,end_date):
    ddbnv_df = ddbapi.get_factor(["netvalue","cum_netvalue"],codes,start_date,end_date)
    def format_ddbmv_df(ddbnv_df):
        ddbnv_df = ddbnv_df.reset_index().rename(
            columns={
                "datetime": "date",
                "netvalue": "price",
                "cum_netvalue": "cumulative_nav_withdrawal",
            }
        )
        for col in ["date","price","cumulative_nav_withdrawal"]:
            ddbnv_df[col] = ddbnv_df[col].astype(str)
        # 初始化结果列表
        result = []

        # 按指定列进行分组
        for group_key, group_data in ddbnv_df.groupby("code"):
            # 为每个分组创建一个字典
            group_dict = {
                "code": group_key,  # 分组值作为第一个key的值
                "nets": [],  # 初始化数据列表
            }

            # 遍历分组内的每一行，转换为字典并添加到data列表中
            for _, row in group_data.iterrows():
                # 排除分组列，将剩余列转换为字典
                row_dict = row.drop("code").to_dict()
                group_dict["nets"].append(row_dict)

            # 将分组字典添加到结果列表
            result.append(group_dict)

        return result
    
    req = CompanyPriceBatchAdd(appid, appkey)
    # print(format_ddbmv_df(ddbnv_df))
    req.set_params(price_data=format_ddbmv_df(ddbnv_df))
    # res = req.do_request(use_df=True)
    res = req.do_request()
    return res


if __name__ == "__main__":
    # hfn_df = pd.read_csv("第一页.csv")
    # for page in range(1, 64):
    #     hfn_df = get_hfw_products(page=page, pagesize=5000)
    #     upload_hfw_products(hfn_df)
    # crud = BaseCRUD("dfs://infos", "weekly_monthly_reports")
    # with get_db() as session:
    #     data = crud.get(session)
    # data = data[data["weekly_report"]]
    # # codes = data["prod_code"]
    # # api = DDBDataApi()
    # # with get_db() as session:
    # #     api.session = session
    # #     nav = api.get_factor("netvalue",codes=codes.to_list(),start_time="2024-12-31",end_time="2024-12-31")
    # # print(nav)
    # # no_nav_codes =codes[~codes.isin(nav.reset_index()["code"])]
    # # print(data[data["prod_code"].isin(no_nav_codes)])
    # # data[data["prod_code"].isin(no_nav_codes)][["prod_code","prod_name"]].to_excel("no_nav.xlsx")
    # prod_codes = data["prod_code"].to_list()
    # hfn_nav = get_hfw_net_value(prod_codes)
    # upload_hfw_net_value(hfn_nav)

    # print(upload_nv_to_hfv(["SVY007","SQN990","SND029","SAAC57"],pd.Timestamp('2025-06-20'),pd.Timestamp('2025-06-20')))
    print(get_hfw_products(page=330, pagesize=1000))